﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using FirebirdSql.Data.FirebirdClient;
using diploma.core.data.objects;
using diploma.core.data.subobjects;


namespace diploma.core.data
{
    /// <summary>
    /// 
    /// </summary>
    public class FireBirdDataSource : DataSource
    {
        string connectionString;
        public FireBirdDataSource(IEnumerable<string> _settings)
            : base(_settings)
        {
            if (assembly == null)
            {
                assembly = System.Reflection.Assembly.GetExecutingAssembly();
            }
        }

        #region Base Connection Methods
        protected override void connect()
        {
            char ch = System.IO.Path.DirectorySeparatorChar;
            string db = @"" + System.Windows.Forms.Application.StartupPath + ch + "data" + ch + settings[3];
            connectionString = @"Server=" + settings[0] + ";User=" + settings[1] + ";Password=" + settings[2] + ";Database=" + db;
            FbConnection con = new FbConnection();
            try
            {
                con.ConnectionString = connectionString;
                con.Open();
                connection = true;
            }
            catch (Exception ex)
            {
                connection = false;
                if (!dbExists())
                {
                    create();
                }
            }
            if (con != null)
            {
                con.Close();
            }
        }

        private bool dbExists()
        {
            String startUp = System.Windows.Forms.Application.StartupPath + System.IO.Path.DirectorySeparatorChar + "data" + System.IO.Path.DirectorySeparatorChar + settings[3];
            if (!System.IO.File.Exists(startUp))
            {
                return false;
            }
            return true;
        }

        private void create()
        {
            FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
            csb.DataSource = settings[0];
            char ch = System.IO.Path.DirectorySeparatorChar;
            csb.Database = @"" + System.Windows.Forms.Application.StartupPath + ch + "data" + ch + settings[3];
            csb.UserID = settings[1];
            csb.Password = settings[2];

            int pageSize = 16384;
            bool forcedWrites = true;
            bool overwrite = false;
            FbConnection.CreateDatabase(csb.ToString(), pageSize, forcedWrites, overwrite);
            Stream s = getEmbeddedFile(System.Reflection.Assembly.GetExecutingAssembly(), "structure.sql");
            if (s != null)
            {
                String dump = getFileContent(s);
                String[] queries = dump.Split(new char[] { '@' });

                using (FbConnection con = new FbConnection(connectionString))
                {

                    foreach (String query in queries)
                    {
                        if (!string.IsNullOrEmpty(query))
                        {
                            FbCommand cmd = new FbCommand(query, con);
                            cmd.Connection.Open();
                            cmd.ExecuteNonQuery();
                            cmd.Connection.Close();
                        }
                    }
                }
            }
        }
        #endregion;

        #region Property

        public override Property insertProperty(Property _property)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                string query = "insert into properties(title, object_type) values('" + _property.Title + "', " + _property.ObjectType + ")";
                FbCommand cmd = new FbCommand(query, con);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                cmd = new FbCommand("select max(id) from properties", con);
                cmd.Connection.Open();
                long l = (long)cmd.ExecuteScalar();
                _property.SetId(l, true);
                cmd.Connection.Close();
                if (_property.Values.Count > 0)
                {
                    foreach (PropertyValue _value in _property.Values)
                    {
                        query = "insert into propertiesvalues(id_property, pvalue, ptitle) values(" + _value.IdParent + ",'" + _value.Value + "', '" + _value.Title + "')";
                        cmd = new FbCommand(query, con);
                        cmd.Connection.Open();
                        cmd.ExecuteNonQuery();
                        cmd.Connection.Close();
                    }
                }
                FbCommand cmdt = new FbCommand("insert into themesbinds(id_object, object_type, id_theme) values(?,?,?)", con);
                cmdt.Parameters.Add(new FbParameter("id_object", l));
                cmdt.Parameters.Add(new FbParameter("object_type", _property.ObjectType));
                cmdt.Parameters.Add(new FbParameter("id_theme", _property.ThemeId));
                cmdt.Connection.Open();
                cmdt.ExecuteNonQuery();
                cmdt.Connection.Close();
            }
            return _property;
        }
        public override void deleteProperty(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from properties where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }

        public override Property updateProperty(Property _property)
        {
            string query = "update properties set title='" + _property.Title + "' object_type=" + _property.ObjectType + " where id=" + _property.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    FbCommand cmdDeleteValues = new FbCommand("delete from propertiesvalues where id_property=" + _property.Id, con);
                    cmdDeleteValues.Connection.Open();
                    cmdDeleteValues.ExecuteNonQuery();
                    cmdDeleteValues.Connection.Close();
                    foreach (PropertyValue _value in _property.Values)
                    {
                        FbCommand cmdUpdateValues = new FbCommand("insert into propertiesvalues(id_property, ptitle, pvalue) values(" + _value.IdParent + ", '" + _value.Title + "', '" + _value.Value + "')", con);
                        cmdUpdateValues.Connection.Open();
                        cmdUpdateValues.ExecuteNonQuery();
                        cmdUpdateValues.Connection.Close();

                    }
                }
                catch (Exception ex)
                {
                }
            }
            return _property;
        }

        public override List<Property> getAllProperties()
        {
            List<Property> props = new List<Property>();
            string query = "select id, title,object_type from properties order by id";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            long id = reader.GetInt64(0);
                            string title = reader.GetString(1);
                            long objectType = reader.GetInt64(2);
                            FbCommand tid = new FbCommand("select id_theme from themesbinds where id_object=? and object_type=?", con);
                            tid.Parameters.Add(new FbParameter("id_object", id));
                            tid.Parameters.Add(new FbParameter("object_type", objectType));
                            FbDataReader r = tid.ExecuteReader();
                            long themeId = -1;
                            if (r.Read())
                            {
                                themeId = r.GetInt64(0);
                            }
                            props.Add(new Property(id, title, themeId, objectType));
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                    foreach (Property p in props)
                    {
                        FbCommand cmd1 = new FbCommand("select pvalue ,ptitle  from propertiesvalues where id_property=" + p.Id, con);
                        cmd1.Connection.Open();
                        FbDataReader rdr = cmd1.ExecuteReader();

                        if (rdr.HasRows)
                        {
                            while (rdr.Read())
                            {
                                p.Values.Add(new PropertyValue(p.Id, rdr.GetString(1), rdr.GetString(0)));
                            }
                        }
                        rdr.Close();
                        cmd1.Connection.Close();
                    }

                }
                catch (Exception ex)
                {
                }
            }
            return props;

        }

        public override Property getProperty(long _id)
        {
            string query = "select id, title, object_type from properties where id=" + _id;
            Property _property = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows && reader.Read())
                    {
                        long id = reader.GetInt64(0);
                        string title = reader.GetString(1);
                        long objectType = reader.GetInt64(2);
                        FbCommand tid = new FbCommand("select id_theme from themesbinds where id_object=? and object_type=?", con);
                        tid.Parameters.Add(new FbParameter("id_object", id));
                        tid.Parameters.Add(new FbParameter("object_type", objectType));
                        FbDataReader r = tid.ExecuteReader();
                        long themeId = -1;
                        if (r.Read())
                        {
                            themeId = r.GetInt64(0);
                        }
                        _property = new Property(id, title, themeId, objectType);
                    }
                    reader.Close();
                    cmd.Connection.Close();

                    FbCommand cmd1 = new FbCommand("select pvalue ,ptitle  from propertiesvalues where id_property=" + _id, con);
                    cmd1.Connection.Open();
                    FbDataReader rdr = cmd1.ExecuteReader();

                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                            _property.Values.Add(new PropertyValue(_id, rdr.GetString(1), rdr.GetString(0)));
                        }
                    }
                    rdr.Close();
                    cmd1.Connection.Close();


                }
                catch (Exception ex)
                {
                }
            }
            return _property;
        }
        #endregion;

        #region Method
        public override void deleteMethod(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from methods where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }

        public override Method insertMethod(Method _method)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                string query = "insert into methods(title,description) values('" + _method.Title + "','" + _method.Description + "')";
                FbCommand cmd = new FbCommand(query, con);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                cmd = new FbCommand("select max(id) from methods", con);
                cmd.Connection.Open();
                long l = (long)cmd.ExecuteScalar();
                _method.SetId(l, true);
                cmd.Connection.Close();
            }
            return _method;
        }

        public override Method updateMethod(Method _method)
        {
            string query = "update methods set title='" + _method.Title + "' description = '" + _method.Description + "' where id=" + _method.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _method;
        }

        public override List<Method> getAllMethods()
        {
            List<Method> methods = new List<Method>();
            string query = "select id, title, description from methods order by id";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Method tmp = new Method(reader.GetInt64(0), reader.GetString(1), reader.GetString(2));
                            methods.Add(tmp);
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return methods;
        }

        public override Method getMethod(long _id)
        {
            string query = "select id, title, description from methods where id=" + _id;
            Method _method = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        _method = new Method(reader.GetInt64(0), reader.GetString(1), reader.GetString(2));
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _method;
        }
        #endregion

        #region ObjectType
        public override void deleteObjectType(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from objecttypes where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }

        public override ObjectType insertObjectType(ObjectType _objecttype)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                string query = "insert into objecttypes(title) values('" + _objecttype.Title + "')";
                FbCommand cmd = new FbCommand(query, con);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                cmd = new FbCommand("select max(id) from objecttypes", con);
                cmd.Connection.Open();
                long l = (long)cmd.ExecuteScalar();
                _objecttype.SetId(l, true);
                cmd.Connection.Close();
            }
            return _objecttype;
        }

        public override ObjectType updateObjectType(ObjectType _objecttype)
        {
            string query = "update objecttypes set title='" + _objecttype.Title + "' where id=" + _objecttype.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _objecttype;
        }

        public override List<ObjectType> getAllObjectTypes()
        {
            List<ObjectType> object_types = new List<ObjectType>();
            string query = "select id, title from objecttypes order by id";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            ObjectType tmp = new ObjectType(reader.GetInt64(0), reader.GetString(1));
                            object_types.Add(tmp);
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return object_types;
        }

        public override ObjectType getObjectType(long _id)
        {
            string query = "select id, title from objecttypes where id=" + _id;
            ObjectType _objecttype = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        _objecttype = new ObjectType(reader.GetInt64(0), reader.GetString(1));
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _objecttype;
        }

        #endregion

        #region Problem

        public override void deleteProblem(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from problems where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }

        public override Problem insertProblem(Problem _problem)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                FbCommand chkcmd = new FbCommand("select id from problems where title='" + _problem.Title + "' and description='" + _problem.Description + "' and solving='" + _problem.Solving + "' and answer='" + _problem.Answer + "'", con);
                chkcmd.Connection.Open();
                FbDataReader reader = chkcmd.ExecuteReader();
                bool b = reader.Read();
                chkcmd.Connection.Close();
                if (!b)
                {
                    string query = "insert into problems(title,description,solving,answer) values('" + _problem.Title + "','" + _problem.Description + "','" + _problem.Solving + "','" + _problem.Answer + "')";
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    cmd = new FbCommand("select max(id) from problems", con);
                    cmd.Connection.Open();
                    long l = (long)cmd.ExecuteScalar();
                    _problem.SetId(l, true);
                    cmd.Connection.Close();

                    FbCommand cmdt = new FbCommand("insert into themesbinds(id_object, object_type, id_theme) values(?,?,?)", con);
                    cmdt.Parameters.Add(new FbParameter("id_object", l));
                    cmdt.Parameters.Add(new FbParameter("object_type", 5));
                    cmdt.Parameters.Add(new FbParameter("id_theme", _problem.ThemeId));
                    cmdt.Connection.Open();
                    cmdt.ExecuteNonQuery();
                    cmdt.Connection.Close();
                }
            }
            return _problem;
        }

        public override Problem updateProblem(Problem _problem)
        {
            string query = "update problems set title='" + _problem.Title + "' description='" + _problem.Description + "' solving='" + _problem.Solving + "' answer='" + _problem.Answer + "' where id=" + _problem.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _problem;
        }

        public override List<Problem> getAllProblems()
        {
            List<Problem> problems = new List<Problem>();
            string query = "select id, title, description, solving, answer from problems order by id";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            long id = reader.GetInt64(0);
                            string title = reader.GetString(1);
                            string description = reader.GetString(2);
                            string solving = reader.GetString(3);
                            string answer = reader.GetString(4);
                            FbCommand tid = new FbCommand("select id_theme from themesbinds where id_object=? and object_type=?", con);
                            tid.Parameters.Add(new FbParameter("id_object", id));
                            tid.Parameters.Add(new FbParameter("object_type", 5));
                            FbDataReader r = tid.ExecuteReader();
                            long themeId = -1;
                            if (r.Read())
                            {
                                themeId = r.GetInt64(0);
                            }
                            problems.Add(new Problem(id, title, description, solving, answer, themeId));
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return problems;
        }

        public override Problem getProblem(long _id)
        {
            string query = "select id, title, description, solving, answer from problems where id=" + _id;
            Problem _problem = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        long id = reader.GetInt64(0);
                        string title = reader.GetString(1);
                        string description = reader.GetString(2);
                        string solving = reader.GetString(3);
                        string answer = reader.GetString(4);
                        FbCommand tid = new FbCommand("select id_theme from themesbinds where id_object=? and object_type=?", con);
                        tid.Parameters.Add(new FbParameter("id_object", id));
                        tid.Parameters.Add(new FbParameter("object_type", 5));
                        FbDataReader r = tid.ExecuteReader();
                        long themeId = -1;
                        if (r.Read())
                        {
                            themeId = r.GetInt64(0);
                        }
                        _problem = new Problem(id, title, description, solving, answer, themeId);
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _problem;
        }

        #endregion

        #region Statement
        public override void deleteStatement(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from statements where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }

        public override Statement insertStatement(Statement _statement)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                FbCommand chkcmd = new FbCommand("select id from statements where title='" + _statement.Title + "' and content='" + _statement.Content + "'", con);
                chkcmd.Connection.Open();
                FbDataReader reader = chkcmd.ExecuteReader();
                bool b = reader.Read();
                chkcmd.Connection.Close();
                if (!b)
                {
                    string query = "insert into statements(title,content, id_type) values('" + _statement.Title + "','" + _statement.Content + "'," + _statement.IdType + ")";
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    cmd = new FbCommand("select max(id) from statements", con);
                    cmd.Connection.Open();
                    long l = (long)cmd.ExecuteScalar();
                    _statement.SetId(l, true);
                    cmd.Connection.Close();

                    FbCommand cmdt = new FbCommand("insert into themesbinds(id_object, object_type, id_theme) values(?,?,?)", con);
                    cmdt.Parameters.Add(new FbParameter("id_object", l));
                    cmdt.Parameters.Add(new FbParameter("object_type", _statement.IdType));
                    cmdt.Parameters.Add(new FbParameter("id_theme", _statement.ThemeId));
                    cmdt.Connection.Open();
                    cmdt.ExecuteNonQuery();
                    cmdt.Connection.Close();
                }
            }
            return _statement;
        }

        public override Statement updateStatement(Statement _statement)
        {
            string query = "update statements set title='" + _statement.Title + "' content='" + _statement.Content + "' id_type=" + _statement.IdType + " where id=" + _statement.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _statement;
        }

        public override List<Statement> getAllStatements()
        {
            List<Statement> statements = new List<Statement>();
            string query = "select id, title, content, id_type from statements order by id";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            long id = reader.GetInt64(0);
                            string title = reader.GetString(1);
                            string content = reader.GetString(2);
                            long id_type = reader.GetInt64(3);
                            FbCommand tid = new FbCommand("select id_theme from themesbinds where id_object=? and object_type=?", con);
                            tid.Parameters.Add(new FbParameter("id_object", id));
                            tid.Parameters.Add(new FbParameter("object_type", id_type));
                            FbDataReader r = tid.ExecuteReader();
                            long themeId = -1;
                            if (r.Read())
                            {
                                themeId = r.GetInt64(0);
                            }
                            statements.Add(new Statement(id, title, content, id_type, themeId));
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return statements;
        }

        public override Statement getStatement(long _id)
        {
            string query = "select id, title, content, id_type from statements where id=" + _id;
            Statement _statement = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        long id = reader.GetInt64(0);
                        string title = reader.GetString(1);
                        string content = reader.GetString(2);
                        long id_type = reader.GetInt64(3);
                        FbCommand tid = new FbCommand("select id_theme from themesbinds where id_object=? and object_type=?", con);
                        tid.Parameters.Add(new FbParameter("id_object", id));
                        tid.Parameters.Add(new FbParameter("object_type", id_type));
                        FbDataReader r = tid.ExecuteReader();
                        long themeId = -1;
                        if (r.Read())
                        {
                            themeId = r.GetInt64(0);
                        }
                        _statement = new Statement(id, title, content, id_type, themeId);
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _statement;
        }

        #endregion

        #region Theme

        public override void deleteTheme(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from themes where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }

        public override Theme insertTheme(Theme _theme)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                FbCommand chkcmd = new FbCommand("select id from themes where title='" + _theme.Title + "'", con);
                chkcmd.Connection.Open();
                FbDataReader reader = chkcmd.ExecuteReader();
                bool b = reader.Read();
                chkcmd.Connection.Close();
                if (!b)
                {
                    string query = "insert into themes(title, id_parent) values('" + _theme.Title + "'," + _theme.ParentId + ")";
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    cmd = new FbCommand("select max(id) from themes", con);
                    cmd.Connection.Open();
                    long l = (long)cmd.ExecuteScalar();
                    _theme.SetId(l, true);
                    cmd.Connection.Close();
                }
            }
            return _theme;
        }

        public override Theme updateTheme(Theme _theme)
        {
            string query = "update themes set title='" + _theme.Title + "', id_parent=" + _theme.ParentId + " where id=" + _theme.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _theme;
        }

        public override List<Theme> getAllThemes()
        {
            List<Theme> themes = new List<Theme>();
            string query = "select id, title, id_parent from themes order by id_parent";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Theme tmp = new Theme(reader.GetInt64(0), reader.GetString(1), reader.GetInt64(2));
                            themes.Add(tmp);
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return themes;
        }

        public override Theme getTheme(long _id)
        {
            string query = "select id, title, id_parent from themes where id=" + _id;
            Theme _theme = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        _theme = new Theme(reader.GetInt64(0), reader.GetString(1), reader.GetInt64(2));
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _theme;
        }

        #endregion

        #region Theorem

        public override void deleteTheorem(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from theorems where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }

        public override Theorem insertTheorem(Theorem _theorem)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                FbCommand chkcmd = new FbCommand("select id from theorems where title='" + _theorem.Title + "' and formulation='" + _theorem.Formulation + "' and proof='" + _theorem.Proof + "'", con);
                chkcmd.Connection.Open();
                FbDataReader reader = chkcmd.ExecuteReader();
                bool b = reader.Read();
                chkcmd.Connection.Close();
                if (!b)
                {
                    string query = "insert into theorems(title, formulation, proof, id_type) values('" + _theorem.Title + "','" + _theorem.Formulation + "','" + _theorem.Proof + "', " + _theorem.IdType + ")";
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    cmd = new FbCommand("select max(id) from theorems", con);
                    cmd.Connection.Open();
                    long l = (long)cmd.ExecuteScalar();
                    _theorem.SetId(l, true);
                    cmd.Connection.Close();

                    FbCommand cmdt = new FbCommand("insert into themesbinds(id_object, object_type, id_theme) values(?,?,?)", con);
                    cmdt.Parameters.Add(new FbParameter("id_object", l));
                    cmdt.Parameters.Add(new FbParameter("object_type", _theorem.IdType));
                    cmdt.Parameters.Add(new FbParameter("id_theme", _theorem.ThemeId));
                    cmdt.Connection.Open();
                    cmdt.ExecuteNonQuery();
                    cmdt.Connection.Close();
                }
            }
            return _theorem;
        }

        public override Theorem updateTheorem(Theorem _theorem)
        {
            string query = "update theorems set title='" + _theorem.Title + "' formulation='" + _theorem.Formulation + "' proof='" + _theorem.Proof + "' id_type=" + _theorem.IdType + " where id=" + _theorem.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _theorem;
        }

        public override List<Theorem> getAllTheorems()
        {
            List<Theorem> theorems = new List<Theorem>();
            string query = "select id, title, formulation, proof, id_type from theorems order by id";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            long id = reader.GetInt64(0);
                            string title = reader.GetString(1);
                            string formulation = reader.GetString(2);
                            string proof = reader.GetString(3);
                            long id_type = reader.GetInt64(4);
                            FbCommand tid = new FbCommand("select id_theme from themesbinds where id_object=? and object_type=?", con);
                            tid.Parameters.Add(new FbParameter("id_object", id));
                            tid.Parameters.Add(new FbParameter("object_type", id_type));
                            FbDataReader r = tid.ExecuteReader();
                            long themeId = -1;
                            if (r.Read())
                            {
                                themeId = r.GetInt64(0);
                            }
                            theorems.Add(new Theorem(id, title, formulation, proof, id_type, themeId));
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return theorems;
        }

        public override Theorem getTheorem(long _id)
        {
            string query = "select id, title, formulation, proof, id_type from theorems where id=" + _id;
            Theorem _theorem = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        long id = reader.GetInt64(0);
                        string title = reader.GetString(1);
                        string formulation = reader.GetString(2);
                        string proof = reader.GetString(3);
                        long id_type = reader.GetInt64(4);
                        FbCommand tid = new FbCommand("select id_theme from themesbinds where id_object=? and object_type=?", con);
                        tid.Parameters.Add(new FbParameter("id_object", id));
                        tid.Parameters.Add(new FbParameter("object_type", id_type));
                        FbDataReader r = tid.ExecuteReader();
                        long themeId = -1;
                        if (r.Read())
                        {
                            themeId = r.GetInt64(0);
                        }
                        _theorem = new Theorem(id, title, formulation, proof, id_type, themeId);
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _theorem;
        }

        #endregion

        #region Question

        public override List<Question> getAllQuestions()
        {
            List<Question> ques = new List<Question>();
            string query = "select id, content from questions order by id";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Question tmp = new Question(reader.GetInt64(0), reader.GetString(1));
                            FbCommand cc = new FbCommand("select id_property from questionbinds where id_question=" + tmp.Id, con);
                            FbDataReader rd = cc.ExecuteReader();
                            if (rd.HasRows)
                            {
                                while (rd.Read())
                                {
                                    long r = rd.GetInt64(0);
                                    Property p = getProperty(r);
                                    tmp.Properties.Add(p);
                                }
                            }
                            ques.Add(tmp);
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return ques;
        }

        public override Question insertQuestion(Question _question)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                string query = "insert into questions(content) values('" + _question.Content + "')";
                FbCommand cmd = new FbCommand(query, con);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                cmd = new FbCommand("select max(id) from questions", con);
                cmd.Connection.Open();
                long l = (long)cmd.ExecuteScalar();
                _question.SetId(l, true);
                cmd.Connection.Close();
            }
            return _question;
        }

        public override Question updateQuestion(Question _question)
        {
            string query = "update questions set content='" + _question.Content + "' where id=" + _question.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    cmd = new FbCommand("delete from questionbinds where id_question=" + _question.Id, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    foreach (Property p in _question.Properties)
                    {
                        cmd = new FbCommand("insert into questionbinds(id_question, id_property) values(?,?)", con);
                        cmd.Parameters.Add(new FbParameter("id_question", _question.Id));
                        cmd.Parameters.Add(new FbParameter("id_property", p.Id));
                        cmd.Connection.Open();
                        cmd.ExecuteNonQuery();
                        cmd.Connection.Close();
                    }
                }
                catch (Exception ex)
                {
                }
            }
            return _question;
        }

        public override Question getQuestion(long _id)
        {
            string query = "select id, content from questions where id=" + _id;
            Question _question = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        _question = new Question(reader.GetInt64(0), reader.GetString(1));
                        FbCommand cc = new FbCommand("select id_property from questionbinds where id_question=" + _question.Id, con);
                        FbDataReader rd = cc.ExecuteReader();
                        if (rd.HasRows)
                        {
                            while (rd.Read())
                            {
                                Property p = getProperty(rd.GetInt64(0));
                                _question.Properties.Add(p);
                            }
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _question;
        }

        public override void deleteQuestion(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from questions where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }
        #endregion

        #region Term
        public override List<Term> getAllTerms()
        {
            List<Term> terms = new List<Term>();
            string query = "select id, title from terms order by id";
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Term tmp = new Term(reader.GetInt64(0), reader.GetString(1));
                            terms.Add(tmp);
                        }
                    }
                    reader.Close();
                    cmd.Connection.Close();
                    foreach (Term t in terms)
                    {
                        cmd = new FbCommand("select variant from termsvariants where id_term=" + t.Id, con);
                        cmd.Connection.Open();
                        reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                t.Variants.Add(reader.GetString(0));
                            }
                        }
                        reader.Close();
                        cmd.Connection.Close();
                    }
                }
                catch (Exception ex)
                {
                }
            }
            return terms;
        }

        public override Term insertTerm(Term _term)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                string query = "insert into terms(title) values('" + _term.Title + "')";
                FbCommand cmd = new FbCommand(query, con);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
                cmd = new FbCommand("select max(id) from terms", con);
                cmd.Connection.Open();
                long l = (long)cmd.ExecuteScalar();
                _term.SetId(l, true);
                cmd.Connection.Close();
                foreach (string v in _term.Variants)
                {
                    cmd = new FbCommand("insert into termsvariants(variant) values('" + v + "')", con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
            }
            return _term;
        }

        public override Term updateTerm(Term _term)
        {
            string query = "update terms set title='" + _term.Title + "' where id=" + _term.Id;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                    FbCommand cmdDeleteValues = new FbCommand("delete from termsvariants where id_term=" + _term.Id, con);
                    cmdDeleteValues.Connection.Open();
                    cmdDeleteValues.ExecuteNonQuery();
                    cmdDeleteValues.Connection.Close();
                    foreach (string v in _term.Variants)
                    {
                        FbCommand cmdUpdateValues = new FbCommand("insert into termsvariants(id_term, variant) values(" + _term.Id + ", '" + v + "')", con);
                        cmdUpdateValues.Connection.Open();
                        cmdUpdateValues.ExecuteNonQuery();
                        cmdUpdateValues.Connection.Close();

                    }
                }
                catch (Exception ex)
                {
                }
            }
            return _term;
        }

        public override Term getTerm(long _id)
        {
            string query = "select id, title from terms where id=" + _id;
            Term _term = null;
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    FbDataReader reader = cmd.ExecuteReader();

                    if (reader.HasRows)
                    {
                        _term = new Term(reader.GetInt64(0), reader.GetString(1));
                    }
                    reader.Close();
                    cmd.Connection.Close();

                    FbCommand cmd1 = new FbCommand("select variant from termsvariants where id_term=" + _id, con);
                    cmd1.Connection.Open();
                    FbDataReader rdr = cmd1.ExecuteReader();

                    if (rdr.HasRows)
                    {
                        while (rdr.Read())
                        {
                            _term.Variants.Add(rdr.GetString(0));
                        }
                    }
                    rdr.Close();
                    cmd1.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
            return _term;
        }

        public override void deleteTerm(long _id)
        {
            using (FbConnection con = new FbConnection(connectionString))
            {
                try
                {
                    string query = "delete from terms where id=" + _id;
                    FbCommand cmd = new FbCommand(query, con);
                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Connection.Close();
                }
                catch (Exception ex)
                {
                }
            }
        }
        #endregion
    }

}
